package com.jnkj.demo.Dao;

import com.jnkj.demo.pojo.Students;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

@Repository
public class StudentsDaoImpl implements StudentsDao{
    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;
    @Override
    public int addStudent(Students students) {
        String sql="insert into students(statusid,sname,ssex,sage,spnumber,sbanji)"+
                "values(:statusid,:sname,:ssex,:sage,:spnumber,:sbanji)";
        Map<String,Object> map = new HashMap<>();
        map.put("statusid",students.getStatusid());
        map.put("sname",students.getSname());
        map.put("ssex",students.getSsex());
        map.put("sage",students.getSage());
        map.put("spnumber",students.getSpnumber());
        map.put("sbanji",students.getSbanji());
        return jdbcTemplate.update(sql,map);
    }

    @Override
    public int updStudent(Students students) {
        String sql="update students set sname=:sname where statusid=:statusid";
        Map<String,Object> map=new HashMap<>();
        map.put("statusid",students.getStatusid());
        map.put("sname",students.getSname());
        return jdbcTemplate.update(sql,map);
    }

    @Override
    public int del(String statusid) {
        String sql="delete from students where statusid=:statusid";
        Map<String,Object> map =new HashMap<>();
        map.put("statusid",statusid);
        return jdbcTemplate.update(sql,map);
    }

    @Override
    public List select() {
        String sql="select * from students";
        List<Students> studetnsList= jdbcTemplate.query(sql, new RowMapper<Students>() {
            @Override
            public Students mapRow(ResultSet rs, int rowNum) throws SQLException {
                Students students= new Students();
                students.setStatusid(rs.getString("statusid"));
                students.setSname(rs.getString("sname"));
                students.setSsex(rs.getString("ssex"));
                students.setSage(rs.getInt("sage"));
                students.setSpnumber(rs.getString("spnumber"));
                students.setSbanji(rs.getString("sbanji"));
                return students;
            }
        });

        for(Students students : studetnsList){

            System.out.println(students.getStatusid()+ "  "+  students.getSname()+ "  "+ students.getSsex()+ "  "+ students.getSage()+ "  "+ students.getSpnumber()+ "  "+ students.getSbanji());
        }
        return null;
    }

    @Override
    public List dbselect(String statusid) {
        String sql="select c.*,s.sname,s.statusid from classes c,students s where s.sbanji=c.sbanji and statusid=:statusid";
        Map<String,Object> map=new HashMap<>();
        map.put("statusid",statusid);
        Map<String,Object> map1=jdbcTemplate.queryForMap(sql,map);
//          Set keySet = map1.keySet();
//          Iterator iterator=keySet.iterator();
//          while(iterator.hasNext()){
//              Object k=iterator.next();
//              Object v=map1.get(k);
//              System.out.println("k:"+k+"v"+v);
//          }
        System.out.println(map1.get("sbanji"));
        return null;
    }
   //2
    @Override
    public List select2() {
        String sql="select s.* from students s,classes c where s.sbanji=(select c.sbanji where banzhuren='甲老师')";
        List<Students> studetnsList= jdbcTemplate.query(sql, new RowMapper<Students>() {
            @Override
            public Students mapRow(ResultSet rs, int rowNum) throws SQLException {
                Students students= new Students();
                students.setStatusid(rs.getString("statusid"));
                students.setSname(rs.getString("sname"));
                students.setSsex(rs.getString("ssex"));
                students.setSage(rs.getInt("sage"));
                students.setSpnumber(rs.getString("spnumber"));
                students.setSbanji(rs.getString("sbanji"));
                return students;
            }
        });

        for(Students students : studetnsList){

            System.out.println(students.getStatusid()+ "  "+  students.getSname()+ "  "+ students.getSsex()+ "  "+ students.getSage()+ "  "+ students.getSpnumber()+ "  "+ students.getSbanji());
        }
        return null;
    }
//3
    @Override
    public List select3() {
        String sql="select s.* from students s,classes c where s.sbanji=(select c.sbanji where FIND_IN_SET('C',kskc))";
        List<Students> studetnsList= jdbcTemplate.query(sql, new RowMapper<Students>() {
            @Override
            public Students mapRow(ResultSet rs, int rowNum) throws SQLException {
                Students students= new Students();
                students.setStatusid(rs.getString("statusid"));
                students.setSname(rs.getString("sname"));
                students.setSsex(rs.getString("ssex"));
                students.setSage(rs.getInt("sage"));
                students.setSpnumber(rs.getString("spnumber"));
                students.setSbanji(rs.getString("sbanji"));
                return students;
            }
        });

        for(Students students : studetnsList){

            System.out.println(students.getStatusid()+ "  "+  students.getSname()+ "  "+ students.getSsex()+ "  "+ students.getSage()+ "  "+ students.getSpnumber()+ "  "+ students.getSbanji());
        }
        return null;
    }
//5
    @Override
    public List select5() {
        String sql="select s.*,c.banzhuren from students s,classes c where 23<=s.sage and s.sage<=25 and s.sbanji=c.sbanji and s.sbanji in(select sbanji from students where 23<=s.sage and s.sage<=25)";
        List<Students> studetnsList= jdbcTemplate.query(sql, new RowMapper<Students>() {
            @Override
            public Students mapRow(ResultSet rs, int rowNum) throws SQLException {
                Students students= new Students();
                students.setStatusid(rs.getString("statusid"));
                students.setSname(rs.getString("sname"));
                students.setSsex(rs.getString("ssex"));
                students.setSage(rs.getInt("sage"));
                students.setSpnumber(rs.getString("spnumber"));
                students.setSbanji(rs.getString("sbanji"));
                students.setBanzhuren(rs.getString("banzhuren"));
                return students;
            }
        });

        for(Students students : studetnsList){

            System.out.println(students.getStatusid()+ "  "+  students.getSname()+ "  "+ students.getSsex()+ "  "+ students.getSage()+ "  "+ students.getSpnumber()+ "  "+ students.getSbanji()+"  "+students.getBanzhuren());
        }
        return null;
    }

    @Override
    public List select10(String statusid) {
        try{
            String sql="select * from students where statusid=:statusid";
            Map<String,Object> map=new HashMap<>();
            map.put("statusid",statusid);
            Map<String,Object> map1=jdbcTemplate.queryForMap(sql,map);

                System.out.println(map1.get("sbanji"));

        }catch (Exception e){

           System.out.println("空");
        }
        return null;
    }

    @Override
    public List select11() {
        String sql="select s.* from students s,classes c where s.sbanji=(select c.sbanji where FIND_IN_SET('俄语',kskc))";
        List<Students> studentsList=jdbcTemplate.query(sql, new RowMapper<Students>() {
            @Override
            public Students mapRow(ResultSet rs, int rowNum) throws SQLException {
                Students students=new Students();
                students.setStatusid(rs.getString("statusid"));
                students.setSname(rs.getString("sname"));
                students.setSsex(rs.getString("ssex"));
                students.setSage(rs.getInt("sage"));
                students.setSpnumber(rs.getString("spnumber"));
                students.setSbanji(rs.getString("sbanji"));
                return students;
            }
        });
        for(Students students:studentsList){
            System.out.println(students.getStatusid()+"  "+students.getSname()+"  "+students.getSsex()+" "+students.getSage()+" "+students.getSpnumber()+"  "+students.getSbanji());
        }
        return null;
    }

//    @Override
//    public int updClasses(Students students) {
//        String sql="update classes c,students s set c.kbrq='2022-05-26' where c.sbanji=s.sbanji and c.sbanji=(select sbanji from students where sname=:sname)";
//        Map<String,Object> map=new HashMap<>();
//        map.put("sname",students.getSname());
//        return jdbcTemplate.update(sql,map);
//    }

}
